package com.lanvideo.dao;

import com.lanvideo.entity.ExamStudent;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

/**
 * 考试学生关联DAO接口
 */
@Repository
public class ExamStudentDao {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 添加学生到考试
     */
    public int addStudentToExam(Integer examId, Integer studentId) {
        String sql = "INSERT INTO exam_student (exam_id, student_id) VALUES (?, ?)";
        return jdbcTemplate.update(sql, examId, studentId);
    }

    /**
     * 批量添加学生到考试
     */
    public int[] addStudentsToExam(Integer examId, List<Integer> studentIds) {
        String sql = "INSERT INTO exam_student (exam_id, student_id) VALUES (?, ?)";
        List<Object[]> batchArgs = new ArrayList<>();
        for (Integer studentId : studentIds) {
            batchArgs.add(new Object[]{examId, studentId});
        }
        return jdbcTemplate.batchUpdate(sql, batchArgs);
    }

    /**
     * 从考试中移除学生
     */
    public int removeStudentFromExam(Integer examId, Integer studentId) {
        String sql = "DELETE FROM exam_student WHERE exam_id=? AND student_id=?";
        return jdbcTemplate.update(sql, examId, studentId);
    }

    /**
     * 获取考试的所有学生
     */
    public List<ExamStudent> getExamStudents(Integer examId) {
        String sql = "SELECT es.*, u.name as student_name, e.title as exam_title " +
                "FROM exam_student es " +
                "LEFT JOIN user u ON es.student_id = u.id " +
                "LEFT JOIN exam e ON es.exam_id = e.id " +
                "WHERE es.exam_id=? ORDER BY es.created_time DESC";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ExamStudent.class), examId);
    }

    /**
     * 获取学生参与的考试
     */
    public List<ExamStudent> getStudentExams(Integer studentId) {
        String sql = "SELECT es.*, u.name as student_name, e.title as exam_title " +
                "FROM exam_student es " +
                "LEFT JOIN user u ON es.student_id = u.id " +
                "LEFT JOIN exam e ON es.exam_id = e.id " +
                "WHERE es.student_id=? ORDER BY es.created_time DESC";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ExamStudent.class), studentId);
    }

    /**
     * 获取学生考试状态
     */
    public ExamStudent getStudentExamStatus(Integer examId, Integer studentId) {
        String sql = "SELECT es.*, u.name as student_name, e.title as exam_title " +
                "FROM exam_student es " +
                "LEFT JOIN user u ON es.student_id = u.id " +
                "LEFT JOIN exam e ON es.exam_id = e.id " +
                "WHERE es.exam_id=? AND es.student_id=?";
        List<ExamStudent> examStudents = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ExamStudent.class), examId, studentId);
        return examStudents.isEmpty() ? null : examStudents.get(0);
    }

    /**
     * 开始考试
     */
    public int startExam(Integer examId, Integer studentId) {
        String sql = "UPDATE exam_student SET status=1, start_time=? WHERE exam_id=? AND student_id=?";
        return jdbcTemplate.update(sql, LocalDateTime.now(), examId, studentId);
    }

    /**
     * 完成考试
     */
    public int completeExam(Integer examId, Integer studentId, Integer score) {
        String sql = "UPDATE exam_student SET status=2, end_time=?, score=? WHERE exam_id=? AND student_id=?";
        return jdbcTemplate.update(sql, LocalDateTime.now(), score, examId, studentId);
    }

    /**
     * 更新考试分数
     */
    public int updateExamScore(Integer examId, Integer studentId, Integer score) {
        String sql = "UPDATE exam_student SET score=? WHERE exam_id=? AND student_id=?";
        return jdbcTemplate.update(sql, score, examId, studentId);
    }

    /**
     * 检查学生是否已参与考试
     */
    public boolean isStudentInExam(Integer examId, Integer studentId) {
        String sql = "SELECT COUNT(*) FROM exam_student WHERE exam_id=? AND student_id=?";
        return jdbcTemplate.queryForObject(sql, Integer.class, examId, studentId) > 0;
    }

    /**
     * 获取考试学生数量
     */
    public int getExamStudentCount(Integer examId) {
        String sql = "SELECT COUNT(*) FROM exam_student WHERE exam_id=?";
        return jdbcTemplate.queryForObject(sql, Integer.class, examId);
    }

    /**
     * 检查学生是否已经参加过考试
     */
    public boolean hasStudentTakenExam(Integer examId, Integer studentId) {
        String sql = "SELECT COUNT(*) FROM exam_student WHERE exam_id=? AND student_id=? AND status=2";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class, examId, studentId);
        return count != null && count > 0;
    }

    /**
     * 添加考试学生记录
     */
    public int addExamStudent(ExamStudent examStudent) {
        String sql = "INSERT INTO exam_student (exam_id, student_id, score, status) VALUES (?, ?, ?, ?)";
        return jdbcTemplate.update(sql, examStudent.getExamId(), examStudent.getStudentId(), 
                examStudent.getScore(), examStudent.getStatus());
    }
}
